package com.lyf.dao.impl;

import com.lyf.dao.EmployeeDao;
import com.lyf.pojo.Employee;
import com.lyf.pojo.User;
import com.lyf.utils.DBTools;
import com.lyf.utils.PageModel;
import com.lyf.vo.CountVoByName;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class EmployeeDaoImpl implements EmployeeDao {
    QueryRunner qr = DBTools.getQueryRunner();
    @Override
    public void add(Employee employee) {
        String sql = "insert into employee_inf(" + "dept_id," + "job_id," + "name," + "card_id,"
                + "address," + "province," + "postCode," + "phone," + "qqNum," + "email," + "sex," + "party,"
                + "birthday," + "race," + "education," + "speciality," + "hobby," + "remark,"
                + "createDate) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        List<Object> params = new ArrayList<>();
        params.add(employee.getDept_id());
        params.add(employee.getJob_id());
        params.add(employee.getName());
        params.add(employee.getCard_id());
        params.add(employee.getAddress());
        params.add(employee.getProvince());
        params.add(employee.getPostCode());
        params.add(employee.getPhone());
        params.add(employee.getQqNum());
        params.add(employee.getEmail());
        params.add(employee.getSex());
        params.add(employee.getParty());
        params.add(employee.getBirthday());
        params.add(employee.getRace());
        params.add(employee.getEducation());
        params.add(employee.getSpeciality());
        params.add(employee.getHobby());
        params.add(employee.getRemark());
        params.add(employee.getCreateDate());
        try {
            qr.update(sql,params.toArray());
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                qr.getDataSource().getConnection().close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    @Override
    public void delete(Integer id) {
        String sql = "update employee_inf set isdelete = 1 where id = ?";
        try {
            qr.update(sql,id);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                qr.getDataSource().getConnection().close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }


    @Override
    public void deleteByDeptId(Integer id) {
        String sql = "UPDATE employee_inf SET isdelete = 1 WHERE dept_id = ?";
        try {
            qr.update(sql,id);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                qr.getDataSource().getConnection().close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    @Override
    public void deleteByJobId(Integer id) {
        String sql = "UPDATE employee_inf  SET isdelete = 1 WHERE job_id = ?";
        try {
            qr.update(sql,id);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                qr.getDataSource().getConnection().close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    @Override
    public void update(Employee employee) {
        String sql = "update employee_inf set dept_id = ?,job_id = ?,name = ?,card_id = ?,address = ?," +
                "province = ?,postCode = ?,phone = ?,qqNum = ?,email = ?,sex = ?,party = ?,birthday = ?," +
                "race = ?,education = ?,speciality = ?,hobby = ?,remark = ?,status = ? where id = ?";

        List<Object> params = new ArrayList<>();
        params.add(employee.getDept_id());
        params.add(employee.getJob_id());
        params.add(employee.getName());
        params.add(employee.getCard_id());
        params.add(employee.getAddress());
        params.add(employee.getProvince());
        params.add(employee.getPostCode());
        params.add(employee.getPhone());
        params.add(employee.getQqNum());
        params.add(employee.getEmail());
        params.add(employee.getSex());
        params.add(employee.getParty());
        params.add(employee.getBirthday());
        params.add(employee.getRace());
        params.add(employee.getEducation());
        params.add(employee.getSpeciality());
        params.add(employee.getHobby());
        params.add(employee.getRemark());
        params.add(employee.getStatus());
        params.add(employee.getId());

        try {
            qr.update(sql,params.toArray());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                qr.getDataSource().getConnection().close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

    }

    @Override
    public void updateStatus(Integer status , Integer id) {
        String sql = "update employee_inf set status = ? where id=?";
        try {
            qr.update(sql,status,id);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                qr.getDataSource().getConnection().close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    @Override
    public List<Employee> selectAll() {
        return null;
    }

    @Override
    public List<CountVoByName> selectCountByDeptId() {
        StringBuilder sql = new StringBuilder("SELECT dept_inf.name as name , COUNT(*) as value FROM employee_inf JOIN dept_inf ON dept_id = dept_inf.id WHERE dept_inf.isdelete = 0 GROUP BY  dept_id");
        List<CountVoByName> countVoByName = null;
        // 执行查询
        try{
            countVoByName =  qr.query(sql.toString(), new BeanListHandler<>(CountVoByName.class));

            return countVoByName;
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }finally {
            try{
                qr.getDataSource().getConnection().close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }
    }

    @Override
    public List<CountVoByName> selectCountByJobId() {
        StringBuilder sql = new StringBuilder("SELECT job_inf.name as name , COUNT(*) as value FROM employee_inf JOIN job_inf ON job_id = job_inf.id WHERE job_inf.isdelete = 0 GROUP BY job_id ");
        List<CountVoByName> countVoByName = null;
        // 执行查询
        try{
            countVoByName =  qr.query(sql.toString(), new BeanListHandler<>(CountVoByName.class));

            return countVoByName;
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }finally {
            try{
                qr.getDataSource().getConnection().close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }
    }

    @Override
    public List<CountVoByName> selectCountBySpeciality() {
        StringBuilder sql = new StringBuilder("SELECT speciality as name , COUNT(*) as value FROM employee_inf WHERE employee_inf.isdelete = 0 GROUP BY speciality");
        List<CountVoByName> countVoByName = null;
        // 执行查询
        try{
            countVoByName =  qr.query(sql.toString(), new BeanListHandler<>(CountVoByName.class));

            return countVoByName;
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }finally {
            try{
                qr.getDataSource().getConnection().close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }
    }

    @Override
    public List<CountVoByName> selectCountByEducation() {
        StringBuilder sql = new StringBuilder("SELECT education as name , COUNT(*) as value FROM employee_inf WHERE employee_inf.isdelete = 0 GROUP BY education");
        List<CountVoByName> countVoByName = null;
        // 执行查询
        try{
            countVoByName =  qr.query(sql.toString(), new BeanListHandler<>(CountVoByName.class));

            return countVoByName;
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }finally {
            try{
                qr.getDataSource().getConnection().close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }
    }

    @Override
    public List<CountVoByName> selectCountByRace() {
        StringBuilder sql = new StringBuilder("SELECT race as name , COUNT(*) as value FROM employee_inf WHERE employee_inf.isdelete = 0 GROUP BY race");
        List<CountVoByName> countVoByName = null;
        // 执行查询
        try{
            countVoByName =  qr.query(sql.toString(), new BeanListHandler<>(CountVoByName.class));

            return countVoByName;
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }finally {
            try{
                qr.getDataSource().getConnection().close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }
    }

    @Override
    public List<CountVoByName> selectCountByProvince() {
        StringBuilder sql = new StringBuilder("SELECT province as name , COUNT(*) as value FROM employee_inf WHERE employee_inf.isdelete = 0 GROUP BY province");
        List<CountVoByName> countVoByName = null;
        // 执行查询
        try{
            countVoByName =  qr.query(sql.toString(), new BeanListHandler<>(CountVoByName.class));

            return countVoByName;
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }finally {
            try{
                qr.getDataSource().getConnection().close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }
    }


    @Override
    public List<CountVoByName> selectCountByParty() {
        StringBuilder sql = new StringBuilder("SELECT party as name , COUNT(*) as value FROM employee_inf WHERE employee_inf.isdelete = 0 GROUP BY party");
        List<CountVoByName> countVoByName = null;
        // 执行查询
        try{
            countVoByName =  qr.query(sql.toString(), new BeanListHandler<>(CountVoByName.class));

            return countVoByName;
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }finally {
            try{
                qr.getDataSource().getConnection().close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }
    }

    @Override
    public Employee selectById(Integer id) {
        StringBuffer sql = new StringBuffer();
        sql.append(" select t.* from( select e.*,j.name as job_name from "
                + " (select e.*,d.name as dept_name from employee_inf e " + "  left join dept_inf d on e.dept_id=d.id"
                + " ) e left join job_inf j on e.job_id=j.id ) t where id = ?");

        sql.append(" AND isdelete = 0");
        ResultSetHandler<Employee> rsh = new BeanHandler<>(Employee.class);
        Employee employee = null;
        try {
            employee = qr.query(sql.toString(),rsh,id);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                qr.getDataSource().getConnection().close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return employee;
    }

    @Override
    public Employee selectByname(String name) {

        StringBuffer sql = new StringBuffer();
        sql.append(" select t.* from( select e.*,j.name as job_name from "
                + " (select e.*,d.name as dept_name from employee_inf e " + "  left join dept_inf d on e.dept_id=d.id"
                + " ) e left join job_inf j on e.job_id=j.id ) t where name = ?");

        sql.append(" AND isdelete = 0");
        ResultSetHandler<Employee> rsh = new BeanHandler<>(Employee.class);
        Employee employee = null;
        try {
            employee = qr.query(sql.toString(),rsh,name);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                qr.getDataSource().getConnection().close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return employee;
    }

    @Override
    public List<Employee> queryEmployee(Employee employee, PageModel model) {

        StringBuffer sql = new StringBuffer();
        List<Object> params = new ArrayList<>(); // 用于存放参数的列表
        sql.append(" select t.* from( select e.*,j.name as job_name from "
                + " (select e.*,d.name as dept_name from employee_inf e " + "  left join dept_inf d on e.dept_id=d.id"
                + " ) e left join job_inf j on e.job_id=j.id ) t where 1=1");



        sql.append(" AND isdelete = 0");
        if (employee!= null && employee.getJob_id()!= null) {
            sql.append(" and job_id = ?");
            params.add(employee.getJob_id());
        }

        if (employee!= null && employee.getDept_id()!= null) {
            sql.append(" and dept_id = ?");
            params.add(employee.getDept_id());
        }

        if (employee.getName() != null && !employee.getName().equals("")) {
            sql.append(" and name like ?");
            params.add("%" + employee.getName() + "%");
        }

        if (employee.getCard_id() != null && !employee.getCard_id().equals("")) {
            sql.append(" and card_id like ?");
            params.add("%" + employee.getCard_id() + "%");
        }

        if (employee.getPhone() != null && !employee.getPhone().equals("")) {
            sql.append(" and phone like ?");
            params.add("%" + employee.getPhone() + "%");
        }

        if (employee.getSex() != null) {
            sql.append(" and sex = ?");
            params.add(employee.getSex());
        }

        if (employee.getStatus() != null) {
            sql.append(" and status = ?");
            params.add(employee.getStatus());
        }
        // 如果有其他条件，以同样的方式添加到 sql 和 params

        // 添加分页的 LIMIT 和 OFFSET
        int pageSize = model.getPageSize();
        int startRow = model.getStartRow();
        sql.append(" LIMIT ? OFFSET ?");
        params.add(pageSize);
        params.add(startRow);

        // 执行查询
        try {
            List<Employee> employeeList = qr.query(sql.toString(), new BeanListHandler<>(Employee.class), params.toArray());
            return employeeList;
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }finally {
            try{
                qr.getDataSource().getConnection().close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }
    }

    @Override
    public int getTotalEmployeeCount(Employee employee) {
        StringBuffer sql = new StringBuffer();
        List<Object> params = new ArrayList<>(); // 用于存放参数的列表
        sql.append(" select count(*) from( select e.*,j.name as job_name from "
                + " (select e.*,d.name as dept_name from employee_inf e " + "  left join dept_inf d on e.dept_id=d.id"
                + " ) e left join job_inf j on e.job_id=j.id ) t where 1=1");

//        sql.append("select t.id as id ,t.dept_id as dept_id,t.job_id as job_id,t.card_id as card_id , t.address as address ,t.province as province , t.post_code as post_code , t.phone as phone , t.qq_num as qq_num , t.email as email ,t.sex as sex ,t.party as party , t.birthday as birthday," +
//                "t.race as race ,t.education as education ,t.speciality as spciality ,t.hobby as hobby ,t.remark as remark ,t.status as status ,t.create_date as create_date , t.isdelete as isdelete , t.dept_name as dept_name ,t.job_name as job_name" +
//                "from (select e.*,j.name as job_name " +
//                "from (select e.*,d.name as dept_name from employee_inf left join dept_inf d on e.dept_id=d.id) e " +
//                "left join job_inf j on e.job_id=j.id ) t where 1=1");

        sql.append(" AND isdelete = 0");
        if (employee!= null && employee.getJob_id()!= null) {
            sql.append(" and job_id = ?");
            params.add(employee.getJob_id());
        }

        if (employee!= null && employee.getDept_id()!= null) {
            sql.append(" and dept_id = ?");
            params.add(employee.getDept_id());
        }

        if (employee.getName() != null && !employee.getName().equals("")) {
            sql.append(" and name like ?");
            params.add("%" + employee.getName() + "%");
        }

        if (employee.getCard_id() != null && !employee.getCard_id().equals("")) {
            sql.append(" and card_id like ?");
            params.add("%" + employee.getCard_id() + "%");
        }

        if (employee.getPhone() != null && !employee.getPhone().equals("")) {
            sql.append(" and phone like ?");
            params.add("%" + employee.getPhone() + "%");
        }

        if (employee.getSex() != null) {
            sql.append(" and sex = ?");
            params.add(employee.getSex());
        }

        if (employee.getStatus() != null) {
            sql.append(" and status = ?");
            params.add(employee.getStatus());
        }
        // 执行查询
        ResultSetHandler<Long> countHandler = new ScalarHandler<>();
        try{
            Long count = qr.query(sql.toString(), countHandler, params.toArray());

            return count.intValue();
        }catch (Exception e){
            e.printStackTrace();
            return 0;
        }finally {
            try{
                qr.getDataSource().getConnection().close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }
    }
}
